﻿using System;
using System.Data.SqlClient;
using System.Linq;
using NUnit.Framework;
using PropertyExpression.ControllableQuery.Test;
using PropertyExpression.ControllableQuery.Toolkit.Test.Queries;

namespace PropertyExpression.ControllableQuery.Toolkit.Test
{
    [TestFixture]
    public class MainTest
    {
        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = @"System.Data.SqlClient.SqlException occurred
Message = 'Invalid column name 'LanguageCode2'.'
QueryType = 'PropertyExpression.ControllableQuery.Toolkit.Test.Queries.Error1Query'
LineNumber = '44'
SqlCommandTextWithLineNumbers = 
  1:DECLARE @p0 VARBINARY(MAX)
  2:DECLARE @p1 VARBINARY(MAX)
  3:DECLARE @p2 VARBINARY(MAX)
  4:DECLARE @p3 VARBINARY(MAX)
  5:DECLARE @p4 VARBINARY(MAX)
  6:
  7:SELECT
  8:    BillOfMaterialsLine.PartNumber
  9:FROM
 10:    BillOfMaterialsLine
 11:WHERE
 12:    BillOfMaterialsHeaderId = 
 13:@p0
 14:
 15:    AND (
 16:         (
 17:          BillOfMaterialsLine.MainLinkExcludedFrom = 0
 18:          AND EXISTS ( SELECT
 19:                        1
 20:                       FROM
 21:                        MainPartLink
 22:                       WHERE
 23:                        MainPartLink.PartNumber = BillOfMaterialsLine.PartNumber
 24:                        AND MainPartLink.ModelPublicationId = 
 25:@p1
 26:
 27:                        AND MainPartLink.ServicePartsPageId <> 
 28:@p2
 29: )
 30:         )
 31:         OR (
 32:             BillOfMaterialsLine.InnerLinkExcludedFrom = 0
 33:             AND EXISTS ( SELECT
 34:                            1
 35:                          FROM
 36:                            InnerPartLink
 37:                            INNER JOIN Model
 38:                                ON InnerPartLink.ModelCode = Model.ModelCode
 39:                                   AND InnerPartLink.ModelSequence = Model.ModelSequence
 40:                            INNER JOIN Publication
 41:                                ON InnerPartLink.PublicationId = Publication.PublicationId
 42:                            INNER JOIN LocalizedPublication
 43:                                ON InnerPartLink.PublicationId = LocalizedPublication.PublicationId
*44:                                   AND LocalizedPublication.LanguageCode2  = 
 45:@p3
 46:
 47:                          WHERE
 48:                            InnerPartLink.PartNumber = BillOfMaterialsLine.PartNumber
 49:                            AND Model.ModelId = 
 50:@p4
 51:
 52:                            AND Publication.FollowingPublicationId IS NULL
 53:						)
 54:            )
 55:        )
SqlCommandText = 
DECLARE @p0 VARBINARY(MAX)
DECLARE @p1 VARBINARY(MAX)
DECLARE @p2 VARBINARY(MAX)
DECLARE @p3 VARBINARY(MAX)
DECLARE @p4 VARBINARY(MAX)

SELECT
    BillOfMaterialsLine.PartNumber
FROM
    BillOfMaterialsLine
WHERE
    BillOfMaterialsHeaderId = 
@p0

    AND (
         (
          BillOfMaterialsLine.MainLinkExcludedFrom = 0
          AND EXISTS ( SELECT
                        1
                       FROM
                        MainPartLink
                       WHERE
                        MainPartLink.PartNumber = BillOfMaterialsLine.PartNumber
                        AND MainPartLink.ModelPublicationId = 
@p1

                        AND MainPartLink.ServicePartsPageId <> 
@p2
 )
         )
         OR (
             BillOfMaterialsLine.InnerLinkExcludedFrom = 0
             AND EXISTS ( SELECT
                            1
                          FROM
                            InnerPartLink
                            INNER JOIN Model
                                ON InnerPartLink.ModelCode = Model.ModelCode
                                   AND InnerPartLink.ModelSequence = Model.ModelSequence
                            INNER JOIN Publication
                                ON InnerPartLink.PublicationId = Publication.PublicationId
                            INNER JOIN LocalizedPublication
                                ON InnerPartLink.PublicationId = LocalizedPublication.PublicationId
                                   AND LocalizedPublication.LanguageCode2  = 
@p3

                          WHERE
                            InnerPartLink.PartNumber = BillOfMaterialsLine.PartNumber
                            AND Model.ModelId = 
@p4

                            AND Publication.FollowingPublicationId IS NULL
						)
            )
        )
Class = '16'
Number = '207'
State = '1'
ErrorCode = '-2146232060'
Procedure = ''
Server = '(local)'
Source = '.Net SqlClient Data Provider'")]
        public void Error1Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error1Query(
                                    queryContext.CreateParam<Guid>(),
                                    queryContext.CreateParam<Guid>(),
                                    queryContext.CreateParam<Guid>(),
                                    new WithInnerPartLinkSwitcher(
                                        true,
                                        queryContext.CreateParam<string>(),
                                        queryContext.CreateParam<Guid>())
                                    )
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "SqlParam already generated.")]
        public void Error2Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error2Query(
                                    queryContext.CreateParam<string>()
                                    )
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = @"'Must declare the scalar variable ""@p0AND", MatchType = MessageMatch.Contains)]
        public void Error12Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error12Query(
                                    queryContext.CreateParam<string>(),
                                    queryContext.CreateParam<string>()
                                    )
                );
        }

        [Test]
        public void Error2FixQuery()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error2FixQuery(
                                    queryContext.CreateParam<string>()
                                    )
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(SqlException), ExpectedMessage = "The variable name '@p1' has already been declared. Variable names must be unique within a query batch or stored procedure.")]
        public void Error3FixQuery()
        {
            ControllableQuery.Test.MainTest.ExecuteReader(
                queryContext => new Error3Query(
                                    queryContext.CreateParam("ModelCode1"),
                                    queryContext.CreateParam("ModelSequence1")
                                    )
                ).ToList();
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "The variable name '@p1' has already been declared. Variable names must be unique within a query batch or stored procedure.", MatchType = MessageMatch.Contains)]
        public void Error3FixQueryQueryChecker()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error3Query(
                                    queryContext.CreateParam<string>(),
                                    queryContext.CreateParam<string>()
                                    )
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "Cannot find data type IPublicationCodeType2.", MatchType = MessageMatch.Contains)]
        public void Error4Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error4Query(
                                    queryContext.CreateTableValuedParam<IPublicationCodeType2>()
                                    )
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "The type of parameter '@p0' was incorrect. Expected SQL Server Database Engine type DataType = 'nvarchar(max)' but was .NET Framework type 'System.Guid'.", MatchType = MessageMatch.Contains)]
        public void Error5Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error5Query(
                                    queryContext.CreateParam<Guid>(),
                                    queryContext.CreateParam<string>()
                                    )
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "The type of parameter '@p1' was incorrect. Expected SQL Server Database Engine type DataType = 'nvarchar(max)' but was .NET Framework type 'System.Int32'.", MatchType = MessageMatch.Contains)]
        public void Error6Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error6Query(
                                    queryContext.CreateParam<string>(),
                                    queryContext.CreateParam<int>()
                                    )
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "The type of parameter '@p0' was incorrect. Expected SQL Server Database Engine type DataType = 'int' but was .NET Framework type 'System.String'.", MatchType = MessageMatch.Contains)]
        public void Error7Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error7Query(
                                    queryContext.CreateParam<string>()
                                    )
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "The type of query result property was incorrect ColumnName = 'PublicationTypeCode'. Expected SQL Server Database Engine type DataType = 'int', AllowDBNull = 'True' but was .NET Framework type 'System.Int32'.", MatchType = MessageMatch.Contains)]
        public void Error8Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error8Query()
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "The type of query result property was incorrect ColumnName = 'PublicationTypeCode'. Expected SQL Server Database Engine type DataType = 'int', AllowDBNull = 'True' but was .NET Framework type 'System.String'.", MatchType = MessageMatch.Contains)]
        public void Error9Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error9Query()
                );
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "The query result type does not contain a definition for property 'PublicationTypeCode'.", MatchType = MessageMatch.Contains)]
        public void Error10Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error10Query()
                );
        }

        private static void CheckQueries<T>(string connectionString, params Func<CheckQueryContext, IQuery<T>>[] queryFuncs)
        {
            QueryChecker.CheckQueries(connectionString, ControllableQuery.Test.QueryCheck.MainTest.ParamTypeChecker, ControllableQuery.Test.QueryCheck.MainTest.QueryResultPropertyTypeChecker, queryFuncs);
        }

        [Test]
        [ExpectedException(ExpectedException = typeof(QueryCheckException), ExpectedMessage = "The query result does not contain a column 'PublicationTypeCode2'.", MatchType = MessageMatch.Contains)]
        public void Error11Query()
        {
            CheckQueries(
                ConnectionString,
                queryContext => new Error11Query()
                );
        }

        private static string ConnectionString
        {
            get { return ControllableQuery.Test.MainTest.ConnectionString; }
        }
    }
}